package collector.storage.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Properties;
import java.util.Map.Entry;

import collector.data.Currency;
import collector.data.Stock;
import collector.data.StockList;
import collector.data.StockMarket;
import collector.storage.DataStorage;

public class MySQLStorage extends DataStorage {
	private Connection connection;

	public MySQLStorage() {
		connection = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Properties properties = new Properties();
			properties.put("useUnicode", "true");
			properties.put("characterEncoding", "UTF-8");
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost/binance?"
							+ "user=binance&password=binance", properties);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public HashMap<String, String> getFieldsFrom(String table, String cField,
			String cValue) {
		// System.out.println("getFieldsFrom "+table+" "+id);

		HashMap<String, String> fields = new HashMap<String, String>();

		try {
			ResultSet rs = executeQuery("SELECT * FROM " + table + " WHERE "
					+ cField + "='" + cValue + "';");
			rs.first();
			ResultSetMetaData metaData = rs.getMetaData();
			for (int i = 1; i <= metaData.getColumnCount(); i++) {
				String columnName = metaData.getColumnName(i);
				String columnValue = rs.getString(columnName);
				fields.put(columnName, columnValue);
			}

		} catch (Exception e) {
			// e.printStackTrace();
		}

		return fields;
	}

	private boolean pairExists(String table, String cField, String cValue,
			String cField2, String cValue2) {

		try {
			ResultSet rs = executeQuery("SELECT * FROM " + table + " WHERE "
					+ cField + "='" + cValue + "' AND " + cField2 + "='"
					+ cValue2 + "';");
			return rs.first();
		} catch (Exception e) {
		}
		return false;
	}

	private ResultSet executeQuery(String string) throws SQLException {
		ResultSet rs = null;
		try {
			//System.out.println(string);

			Statement stmt = connection.createStatement();

			rs = stmt.executeQuery(string);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rs;
	}

	private void executeUpdate(String string) {
		try {
			//System.out.println(string);

			Statement stmt = connection.createStatement();

			stmt.executeUpdate(string);
		} catch (Exception e) {
			e.printStackTrace();
			System.exit(1);
		}
	}

	@Override
	protected void addList(StockList stockList, StockMarket stockMarket) {
		String stockmarket_id = getIdFromCode("stockmarket",stockMarket.getCode());
		
		executeUpdate("INSERT INTO stocklist (code,stockmarket_id,version) VALUES ('"
				+ stockList.getName() + "','"
				+ stockmarket_id + "','0');");
	}

	private String getIdFromCode(String table, String code) {
		HashMap<String, String> stockListFields = getFieldsFrom(table,
				"code", code);
		return stockListFields.get("id");
	}

	@Override
	protected void addMarket(StockMarket stockMarket) {
		executeUpdate("INSERT INTO stockmarket (code, name,version) VALUES ('"
				+ stockMarket.getCode() + "','" + stockMarket.getName() + "','0');");
	}

	@Override
	protected void addCurrency(Currency currency) {
		executeUpdate("INSERT INTO currency (code, sek, version) VALUES ('"
				+ currency.getName() + "','" + currency.getSEK() + "','0');");
	}

	@Override
	protected void addStock(Stock stock, StockList stockList) {

		includeCurrency(stock.getCurrency());

		String stockListId = getIdFromCode("stocklist",stockList.getName());

		String currency_id = getIdFromCode("currency",stock.getCurrency().getName());
		
		executeUpdate("INSERT INTO stock (url, value, highest, lowest, time, code, diff, diffproc, currency_id, name, stocklist_id, version) VALUES ('"
				+ stock.getUrl()
				+ "','"
				+ stock.getValue()
				+ "','"
				+ stock.getHighest()
				+ "','"
				+ stock.getLowest()
				+ "','"
				+ stock.getTime()
				+ "','"
				+ stock.getShortname()
				+ "','"
				+ stock.getChange()
				+ "','"
				+ stock.getChangeProc()
				+ "','"
				+ currency_id
				+ "','"
				+ stock.getName()
				+ "','" + stockListId + "','0');");
	}

	@Override
	protected boolean currencyExists(Currency currency) {
		HashMap<String, String> result = getFieldsFrom("currency", "code",
				currency.getName());
		if (result.size() > 0)
			return true;
		return false;
	}

	@Override
	protected void updateCurrency(Currency currency) {
		executeUpdate("UPDATE currency SET sek='" + currency.getSEK()
				+ "' WHERE code='" + currency.getName() + "';");
	}

	@Override
	protected boolean listExists(StockMarket market, StockList stockList) {
		String marketId = getIdFromCode("stockmarket",market.getCode());

		return pairExists("stocklist", "code", stockList.getName(),"stockmarket_id",marketId);
	}

	@Override
	protected boolean marketExists(StockMarket market) {
		HashMap<String, String> result = getFieldsFrom("stockmarket", "code",
				market.getCode());
		if (result.size() > 0)
			return true;
		return false;
	}

	@Override
	protected boolean stockExists(Stock stock, StockList stockList) {
		String stockListId = getIdFromCode("stocklist",stockList.getName());
		return pairExists("stock", "code", stock
				.getShortname(),"stocklist_id",stockListId);
	}

	@Override
	protected void updateStock(Stock stock, StockList stockList) {

		String stockListId = getIdFromCode("stocklist",stockList.getName());
		
		HashMap<String, String> currencyFields = getFieldsFrom("currency",
				"code", stock.getCurrency().getName());
		String currency_id = currencyFields.get("id");
		
		executeUpdate("UPDATE stock SET url='" + stock.getUrl() + "', value='"
				+ stock.getValue() + "', highest='"
				+ stock.getHighest() + "', lowest='"
				+ stock.getLowest() + "', time='"
				+ stock.getTime() + "', diff='" + stock.getChange()
				+ "', diffproc='"
				+ stock.getChangeProc() 
				+ "', currency_id='" + currency_id
				+ "', name='" + stock.getName().replace("'", "\'")
				+ "' WHERE code='" + stock.getShortname() + "' AND stocklist_id='" + stockListId
				+ "';");
	}

}
